package com.example.nextgen.util.database;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.fastjson.JSON;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

/**
 * @author martin
 * @date Created in 2022/12/15 17:58
 * @description
 */
public class DBInfoUtil {

    private final static String sql = "select tb.TABLE_NAME    as tableName,\n" +
            "       tb.TABLE_COMMENT as tableComment,\n" +
            "       COLUMN_NAME      as columnName,\n" +
            "       COLUMN_DEFAULT   as columnDefault,\n" +
            "       IS_NULLABLE      as isNullable,\n" +
            "       DATA_TYPE        as dataType,\n" +
            "       COLUMN_TYPE      as columnType,\n" +
            "       COLUMN_KEY       as columnKey,\n" +
            "       EXTRA            as extra,\n" +
            "       COLUMN_COMMENT   as columnComment\n" +
            "from information_schema.tables tb,\n" +
            "     information_schema.columns col\n" +
            "where tb.TABLE_NAME = col.TABLE_NAME\n" +
            "  and tb.table_schema = (select database())\n" +
            "order by tb.table_name, ordinal_position";


    public static void main(String[] args) throws SQLException, IllegalAccessException {
        String url = "jdbc:mysql://47.241.33.220:3306/jeepay" +
                "?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8";
        Map<String, TableEntity> dbInfo = getDbInfo(url, "root", "SmallInsert__");
        System.out.println(JSON.toJSONString(dbInfo));
    }

    public static Map<String, TableEntity> getDbInfo(String url, String username, String password) throws SQLException,
            IllegalAccessException {
        Map<String, TableEntity> tables = new HashMap<>();
        Connection connection = null;
        try (DruidDataSource druidDataSource = new DruidDataSource()) {
            druidDataSource.setUrl(url);
            druidDataSource.setUsername(username);
            druidDataSource.setPassword(password);
            connection = druidDataSource.getConnection();
            ResultSet resultSet = connection.createStatement().executeQuery(sql);
            while (resultSet.next()) {
                ColumnEntity column = new ColumnEntity();
                for (Field field : ColumnEntity.class.getDeclaredFields()) {
                    String value = resultSet.getString(field.getName());
                    field.setAccessible(true);
                    field.set(column, value);
                }
                if (tables.containsKey(column.getTableName())) {
                    tables.get(column.getTableName()).getColumns().put(column.getColumnName(), column);
                } else {
                    TableEntity table = new TableEntity();
                    table.setTableName(column.getTableName());
                    table.setTableComment(column.getTableComment());
                    table.setColumns(new HashMap<>());
                    table.getColumns().put(column.getColumnName(), column);
                    tables.put(table.getTableName(), table);
                }
            }
        } finally {
            if (connection != null) {
                connection.close();
            }
        }
        return tables;
    }

}
